package edu.ucsd.cse135.gas.logic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import edu.ucsd.cse135.gas.logic.applicant.Applicant;
import edu.ucsd.cse135.gas.logic.support.Database;

public class GRE {

	private int applicant;
	private int analytical;
	private int analyticalPercent;
	private int verbal;
	private int verbalPercent;
	private int quantitative;
	private int quantitativePercent;
	private int total;
	private int totalPercent;
	private String type;
	private int month;
	private int year;
	private String regNo;

	
	public int getApplicant() {
		return applicant;
	}
	public void setApplicant(int applicant) {
		this.applicant = applicant;
	}
	public int getAnalytical() {
		return analytical;
	}
	public void setAnalytical(int analytical) {
		this.analytical = analytical;
	}
	public int getAnalyticalPercent() {
		return analyticalPercent;
	}
	public void setAnalyticalPercent(int analyticalPercent) {
		this.analyticalPercent = analyticalPercent;
	}
	public int getVerbal() {
		return verbal;
	}
	public void setVerbal(int verbal) {
		this.verbal = verbal;
	}
	public int getVerbalPercent() {
		return verbalPercent;
	}
	public void setVerbalPercent(int verbalPercent) {
		this.verbalPercent = verbalPercent;
	}
	public int getQuantitative() {
		return quantitative;
	}
	public void setQuantitative(int quantitative) {
		this.quantitative = quantitative;
	}
	public int getQuantitativePercent() {
		return quantitativePercent;
	}
	public void setQuantitativePercent(int quantitativePercent) {
		this.quantitativePercent = quantitativePercent;
	}
	public int getTotal() {
		return total;
	}
	public void setTotal(int total) {
		this.total = total;
	}
	public int getTotalPercent() {
		return totalPercent;
	}
	public void setTotalPercent(int totalPercent) {
		this.totalPercent = totalPercent;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public int getMonth() {
		return month;
	}
	public void setMonth(int month) {
		this.month = month;
	}
	public int getYear() {
		return year;
	}
	public void setYear(int year) {
		this.year = year;
	}
	public String getRegNo() {
		return regNo;
	}
	public void setRegNo(String regNo) {
		this.regNo = regNo;
	}
	
	/** Write gre object to database **/
	
	public int writeToDatabase()
	{
		try
		{
			Connection conn = Database.open();
			
			String sql = "INSERT INTO gre (applicant, analytical, analytical_percent, verbal, verbal_percent, " +
						 "quantitative, quantitative_percent, total, total_percent, month, year, reg_no, type)" +
						 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			preStmt.setInt(1, this.getApplicant());
			preStmt.setInt(2, this.getAnalytical());
			preStmt.setInt(3, this.getAnalyticalPercent());
			preStmt.setInt(4, this.getVerbal());
			preStmt.setInt(5, this.getVerbalPercent());
			preStmt.setInt(6, this.getQuantitative());
			preStmt.setInt(7, this.getQuantitativePercent());
			preStmt.setInt(8, this.getTotal());
			preStmt.setInt(9, this.getTotalPercent());
			preStmt.setInt(10, this.getMonth());
			preStmt.setInt(11, this.getYear());
			preStmt.setString(12, this.getRegNo());
			preStmt.setString(13, this.getType());
			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
			
			return 102;
		}
		catch(Exception e)
		{
			Database.close();
			return 204;
		}
	}
	
	/** Get information about the GRE of a specific applicant **/
	
	public boolean getGre(int id) {
		
		try 
		{
			Connection conn = Database.open();
			
			String sql = "SELECT * FROM gre WHERE applicant = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, id);
			
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			this.setAnalytical(rs.getInt("analytical"));
			this.setAnalyticalPercent(rs.getInt("analytical_percent"));
			this.setVerbal(rs.getInt("verbal"));
			this.setVerbalPercent(rs.getInt("verbal_percent"));
			this.setQuantitative(rs.getInt("quantitative"));
			this.setQuantitativePercent(rs.getInt("quantitative_percent"));
			this.setMonth(rs.getInt("month"));
			this.setYear(rs.getInt("year"));
			this.setTotal(rs.getInt("total"));
			this.setTotalPercent(rs.getInt("total_percent"));
			this.setType(rs.getString("type"));
			this.setRegNo(rs.getString("reg_no"));
			this.setApplicant(id);
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return true;
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return false;
		}
		
	}
	
	public static ArrayList<Applicant> getApplicants() {
		
		try 
		{
			Connection conn = Database.open();
			
			String sql = "SELECT applicant.id, applicant.name, applicant.first_name, applicant.mail " +
						 "FROM applicant WHERE applicant.id NOT IN(SELECT applicant FROM gre);";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Applicant> applicants = new ArrayList<Applicant>();
			
			while(rs.next())
			{
				Applicant applicant = new Applicant();
				
				applicant.setApplicantId(rs.getInt("id"));
				applicant.setFirstName(rs.getString("first_name"));
				applicant.setName(rs.getString("name"));
				applicant.setMail(rs.getString("mail"));
				
				applicants.add(applicant);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return applicants;
		}
		catch(Exception e)
		{
			Database.close();
			e.printStackTrace();
			return null;
		}
		
	}

}
